

import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

import org.json.simple.JSONArray;
import org.json.simple.JSONObject;
import org.json.simple.parser.JSONParser;
import org.json.simple.parser.ParseException;

public class JsonToTable {

	private Connection connect = null;
	PreparedStatement preparedStatement = null;
	static final String DB_URL = "jdbc:oracle:thin:@ma-shieldd-ldb01.corp.apple.com:1546:shield1d";
	// Database credentials
	static final String USER = "SHIELD_USER";
	static final String PASS = "passwordtemppasswordtemp4";

	public static void main(String[] args) {
		String test = new JsonToTable()
				.callFile("/users/bhargavitanneru/downloads/apps.json");

		try {
			new JsonToTable().save(test);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public String callFile(String myFile) {
		System.out.println("Requested File:" + myFile);
		StringBuilder stringJson = new StringBuilder();
		
		try {
			
				
				FileReader logReader = new FileReader(myFile);
		        BufferedReader buffer = new BufferedReader(logReader);
		           
				if (buffer != null) {
					int cp;
					while ((cp = buffer.read()) != -1) {
						stringJson.append((char) cp);
					}
					buffer.close();
				} }
			
			

		 catch (Exception e) {
			throw new RuntimeException("Exception while reading File:" + myFile,
					e);
		 }
		

		
		return stringJson.toString();

	}

	public void save(String stringJson) throws Exception {
		int status = 0;
		try {
			// Load the Oracle driver, each DB has its own driver
			Class.forName("oracle.jdbc.driver.OracleDriver");

			// DB connection setup
			System.out.println("Connecting to database...");
			connect = DriverManager.getConnection(DB_URL, USER, PASS);

			if (connect != null) {
				System.out.println("connected successfully");
			}
			// PreparedStatements
			preparedStatement = connect
					.prepareStatement("insert into HOA(ip, app_id, owner_id,role, name, email)"
							+ "values (?,?,?,?,?,?)");

			JSONParser parser = new JSONParser();
			String app_id = null;
			long owner_id; 
			String ip = null, role = null, name=null, email = null;
			

			try {
				Object obj = parser.parse(stringJson.toString());
				JSONObject mainArray = (JSONObject) obj;
				JSONArray jsonMainArr = (JSONArray) mainArray.get("apps");

				System.out.println("total size" + jsonMainArr.size());
				for (int i = 518404; i < jsonMainArr.size(); i++) { // **line 2**

					JSONObject childJSONObject = (JSONObject) jsonMainArr
							.get(i);
					
					ip = (String) childJSONObject.get("ip");
					app_id = (String) childJSONObject.get("app_id");
					owner_id = (Long) childJSONObject.get("owner");
					role = (String) childJSONObject.get("role");
					name = (String) childJSONObject.get("name");
					email = (String) childJSONObject.get("email");
					
					
					// Parameters start with 1
					preparedStatement.setString(1,ip);
					preparedStatement.setString(2, app_id);
					preparedStatement.setLong(3, owner_id);
					preparedStatement.setString(4, role);
					preparedStatement.setString(5, name);
					preparedStatement.setString(6, email);
					
					status = preparedStatement.executeUpdate();
					
				}

			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			System.out.println("insertion completed");
		} catch (Exception e) {
			throw e;
		} finally {
			try {
				if (connect != null) {
					connect.close();
				}

			} catch (Exception e) {

			}
		}
	}
}
